package com.example.mapper;

import com.example.pojo.ManagedClass;
import com.example.pojo.Score;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;

@Mapper
public interface TeacherLastPageMapper {
    @Select("select teacher_classes.class_id,class_name,subject_name,semester,percentage_usual_score,percentage_midterm_score,percentage_final_score from  (select distinct class_id from teacher_classes where teacher_id=#{teacherId})c,teacher_classes,classes where c.class_id=teacher_classes.class_id and teacher_id=#{teacherId} and c.class_id=classes.class_id order by class_id")
    List<ManagedClass> loadAllClasses(Integer teacherId);

    @Select("select subject_name from subjects")
    List<String> searchAllSubjectName();

/*    @Select("select count(1)>0 from teacher_classes where teacher_id=#{teacherId} and class_id=(select class_id from classes where class_name =#{className}) and subject_name=#{subjectName} and semester=#{semester}")
    boolean isExitsManagedClass(ManagedClass managedClass);*/
@Select("select count(1)>0 from teacher_classes where teacher_id=#{teacherId} and class_id=(select class_id from classes where class_name =#{className}) and subject_name=#{subjectName}")
boolean isExitsManagedClass(ManagedClass managedClass);

/*    @Insert("insert into teacher_classes(teacher_id,class_id,subject_name,semester,percentage_usual_score,percentage_midterm_score,percentage_final_score) values(#{teacherId},(select class_id from classes where class_name =#{className}),#{subjectName},#{semester},#{percentageUsualScore},#{percentageMidtermScore},#{percentageFinalScore})")
    void insertManageClass(ManagedClass managedClass);*/
@Insert("insert into teacher_classes(teacher_id,class_id,subject_name,percentage_usual_score,percentage_midterm_score,percentage_final_score) values(#{teacherId},(select class_id from classes where class_name =#{className}),#{subjectName},#{percentageUsualScore},#{percentageMidtermScore},#{percentageFinalScore})")
void insertManageClass(ManagedClass managedClass);


/*    @Select("select count(1)>0 from teacher_classes where teacher_id=#{teacherId} and class_id=(select class_id from classes where class_name =#{newClassName}) and subject_name=#{newSubjectName} and semester=#{newSemester} and percentage_usual_score=#{percentageUsualScore} and percentage_midterm_score=#{percentageMidtermScore} and percentage_final_score=#{percentageFinalScore}")
    boolean isExitsNewManagedClass(ManagedClass managedClass);*/

    @Select("select count(1)>0 from teacher_classes where  class_id=(select class_id from classes where class_name =#{newClassName}) and subject_name=#{newSubjectName}  ")
    boolean isExitsNewManagedClass(ManagedClass managedClass);

  /*  @Update("update teacher_classes set class_id=(select class_id from classes where class_name =#{newClassName}),subject_name=#{newSubjectName},semester=#{newSemester},percentage_usual_score=#{percentageUsualScore},percentage_midterm_score=#{percentageMidtermScore},percentage_final_score=#{percentageFinalScore} where teacher_id=#{teacherId} and class_id=(select class_id from classes where class_name =#{className}) and subject_name=#{subjectName} and semester=#{semester}")
    void updateManageClass(ManagedClass managedClass);*/

    @Update("update teacher_classes set class_id=(select class_id from classes where class_name =#{newClassName}),subject_name=#{newSubjectName},percentage_usual_score=#{percentageUsualScore},percentage_midterm_score=#{percentageMidtermScore},percentage_final_score=#{percentageFinalScore} where teacher_id=#{teacherId} and class_id=(select class_id from classes where class_name =#{className}) and subject_name=#{subjectName}")
    void updateManageClass(ManagedClass managedClass);

 /*   @Delete("delete from teacher_classes where teacher_id=#{teacherId} and class_id=(select class_id from classes where class_name =#{className}) and subject_name=#{subjectName} and semester=#{semester}")
    void deleteManageClass(ManagedClass managedClass);*/

    @Delete("delete from teacher_classes where teacher_id=#{teacherId} and class_id=(select class_id from classes where class_name =#{className}) and subject_name=#{subjectName} ")
    void deleteManageClass(ManagedClass managedClass);

/*    @Select("select grades.student_id,subject_id,semester,usual_score,midterm_score,final_score from grades,(select student_id from students where class_id=(select class_id from classes where class_name =#{className}))s where grades.student_id=s.student_id and subject_id=(select subject_id from subjects where subject_name=#{subjectName}) and semester=#{semester}")
    List<Score> selectManageClassStudentScore(ManagedClass managedClass);*/

    @Select("select grades.student_id,subject_id,usual_score,midterm_score,final_score from grades,(select student_id from students where class_id=(select class_id from classes where class_name =#{className}))s where grades.student_id=s.student_id and subject_id=(select subject_id from subjects where subject_name=#{subjectName})")
    List<Score> selectManageClassStudentScore(ManagedClass managedClass);

    void deleteManageClassStudentScore(List<Score> scores);

    @Select("select credit from subjects where subject_name=#{newSubjectName}")
    Integer inqueryEarnCredit(ManagedClass managedClass);

    void updateManageClassStudentScore(List<Score> scores);

    @Select("select count(1)>0 from teacher_classes where class_id=(select class_id from classes where class_name=#{className}) and subject_name=#{subjectName} ")
    boolean classsHasBeenManaged(String className, String subjectName);

    @Select("select count(1)>0 from teacher_classes where teacher_id=#{teacherId} and class_id=(select class_id from classes where class_name=#{newClassName}) and subject_name=#{newSubjectName}")
    boolean isManagedClass(ManagedClass managedClass);
}
